/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package telsoft.cms_admin.data;

import com.tamnhat.entity.ContentTemplate;
import com.tamnhat.entity.Product_Content;
import com.tamnhat.manager.model.ProductModel;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import telsoft.admin.util.AppServer;
import telsoft.cms_admin.entity.ETT_Product;
import telsoft.cms_admin.entity.ETT_TreeProduct;
import telsoft.sql.Database;
import telsoft.util.DateUtil;

/**
 *
 * @author hoannc
 */
public class DATA_FormTreeProduct implements Serializable {

    private String SysId;
    private SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");

    public List getUser2Group() throws Exception {
        List returnData = new ArrayList<ETT_TreeProduct>();
        Connection cn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String strSQL = " select a.system_id,a.system_name,0 parent_id from sdp_systems a order by a.system_id ";
        try {
//            open();
            // Get User list
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            rs = stmt.executeQuery();
            while (rs.next()) {
                ETT_TreeProduct tree = new ETT_TreeProduct();
                tree.setID(rs.getString(1));
                tree.setNAME(rs.getString(2));
                tree.setPARENT_ID(rs.getString(3));
                tree.setTYPE("0");
                returnData.add(tree);
            }
            Database.closeObject(rs);
            Database.closeObject(stmt);

            // Get Group list
            strSQL = " select b.service_id,b.service_name,b.system_id from sdp_services b order by b.service_id ";
            stmt = cn.prepareStatement(strSQL);
            rs = stmt.executeQuery();
            while (rs.next()) {
                ETT_TreeProduct tree = new ETT_TreeProduct();
                tree.setID(rs.getString(1));
                tree.setNAME(rs.getString(2));
                tree.setPARENT_ID(rs.getString(3));
                tree.setTYPE("1");
                returnData.add(tree);
            }
            Database.closeObject(rs);
            Database.closeObject(stmt);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
//            close();
            Database.closeObject(cn);
        }
        return returnData;
    }

    public List loadDetailProduct(ETT_TreeProduct ETT_TreeProduct) throws Exception {
        List returnData = new ArrayList<ETT_Product>();
        Connection cn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String strSQL = "";
        try {
//            open();
            strSQL = " SELECT product_id, "
                    + " sdp_product_code, "
                    + " product_name, "
                    + " welcome_message, "
                    + " farewell_message, "
                    + " service_number, "
                    + " register_keyword, "
                    + " cancel_keyword, "
                    + " TO_CHAR(created_time,'dd/MM/yyyy hh24:mi:ss'),"
                    + " TO_CHAR(effective_time,'dd/MM/yyyy hh24:mi:ss'),"
                    + " TO_CHAR(expiry_time,'dd/MM/yyyy hh24:mi:ss'),"
                    + " content_builder_class_id,"
                    + " content_builder_type,"
                    + " content_mode,"
                    + " charging_mode,"
                    + " charging_class_id,"
                    + " status, service_id,content_type,effective_duration,"
                    + " build_procedure, build_content, build_user_content, content_concatenation"
                    + " FROM sdp_products"
                    + " WHERE service_id = ? "
                    + " ORDER BY sdp_product_code, product_name";
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            ///////////
            stmt.setString(1, ETT_TreeProduct.getID());

            rs = stmt.executeQuery();
            while (rs.next()) {
                ETT_Product dtDetail = new ETT_Product();
                dtDetail.setPRODUCT_ID(rs.getString(1));
                dtDetail.setSDP_PRODUCT_CODE(rs.getString(2));
                dtDetail.setPRODUCT_NAME(rs.getString(3));
                dtDetail.setWELCOME_MESSAGE(rs.getString(4));
                dtDetail.setFAREWELL_MESSAGE(rs.getString(5));
                dtDetail.setSERVICE_NUMBER(rs.getString(6));
                dtDetail.setREGISTER_KEYWORD(rs.getString(7));
                dtDetail.setCANCEL_KEYWORD(rs.getString(8));
                dtDetail.setCREATED_TIME(DateUtil.toDate(rs.getString(9), "dd/MM/yyyy HH:mm:ss"));
                dtDetail.setEFFECTIVE_TIME(DateUtil.toDate(rs.getString(10), "dd/MM/yyyy HH:mm:ss"));
                if(rs.getString(11) == null){
                    dtDetail.setEXPIRY_TIME(null);
                }
                else dtDetail.setEXPIRY_TIME(DateUtil.toDate(rs.getString(11), "dd/MM/yyyy HH:mm:ss"));
                dtDetail.setStrCREATED_TIME(rs.getString(9));
                dtDetail.setStrEFFECTIVE_TIME(rs.getString(10));
                dtDetail.setStrEXPIRY_TIME(rs.getString(11));
                dtDetail.setCONTENT_BUILDER_CLASS_ID(rs.getString(12));
                dtDetail.setCONTENT_BUILDER_TYPE(rs.getString(13));
                dtDetail.setCONTENT_MODE(rs.getString(14));
                dtDetail.setCHARGING_MODE(rs.getString(15));
                dtDetail.setCHARGING_CLASS_ID(rs.getString(16));
                dtDetail.setSTATUS(rs.getString(17));
                dtDetail.setSERVICE_ID(rs.getString(18));
                dtDetail.setContentType(rs.getString(19));
                dtDetail.setEffective_duration(Integer.parseInt(rs.getString(20)));
                dtDetail.setBuild_procedure(rs.getString("build_procedure"));
                dtDetail.setBuild_content(Integer.parseInt(rs.getString("build_content")));
                dtDetail.setBuild_user_content(Integer.parseInt(rs.getString("build_user_content")));
                dtDetail.setContent_concatenation(Integer.parseInt(rs.getString("content_concatenation")));
                returnData.add(dtDetail);
            }
        } catch (Exception ex) {
            ex.fillInStackTrace();
        } finally {
//            close();
            rs.close();
            stmt.close();
            Database.closeObject(cn);
        }
        return returnData;
    }

    /* public List search(ETT_TreeProduct ETT_TreeProduct, ETT_Product ETT_Product) throws Exception {
     List returnData = new ArrayList<ETT_Product>();
     Connection cn = null;
     PreparedStatement stmt = null;
     ResultSet rs = null;
     String strSQL = "";
     //        try {
     //            open();
     strSQL = " SELECT product_id, "
     + " sdp_product_code, "
     + " product_name, "
     + " welcome_message, "
     + " farewell_message, "
     + " service_number, "
     + " register_keyword, "
     + " cancel_keyword, "
     + " TO_CHAR(created_time,'dd/MM/yyyy hh24:mi:ss'),"
     + " TO_CHAR(effective_time,'dd/MM/yyyy hh24:mi:ss'),"
     + " TO_CHAR(expiry_time,'dd/MM/yyyy hh24:mi:ss'),"
     + " content_builder_class_id,"
     + " content_builder_type,"
     + " content_mode,"
     + " charging_mode,"
     + " charging_class_id,"
     + " status, service_id"
     + " FROM sdp_products"
     + " WHERE service_id = ? ";

     Vector vtParam = new Vector();
     vtParam.addElement(ETT_TreeProduct.getID());
     if (!ETT_Product.getSDP_PRODUCT_CODE().equals("")) {
     strSQL += " and  sdp_product_code like upper('%'||?||'%') ";
     vtParam.addElement(ETT_Product.getSDP_PRODUCT_CODE());
     }
     if (!ETT_Product.getPRODUCT_NAME().equals("")) {
     strSQL += " and product_name like upper('%'||?||'%') ";
     vtParam.addElement(ETT_Product.getPRODUCT_NAME());
     }
     if (!ETT_Product.getSTATUS().equals("")) {
     strSQL += " and status = ? ";
     vtParam.addElement(ETT_Product.getSTATUS());
     }
     strSQL += " ORDER BY sdp_product_code, product_name";

     try {
     //            open();
     cn = AppServer.getConnection();
     stmt = cn.prepareStatement(strSQL);
     for (int i = 0; i < vtParam.size(); i++) {
     stmt.setString(i + 1, vtParam.elementAt(i).toString());
     }
     rs = stmt.executeQuery();
     while (rs.next()) {
     ETT_Product dtDetail = new ETT_Product();
     dtDetail.setPRODUCT_ID(rs.getString(1));
     dtDetail.setSDP_PRODUCT_CODE(rs.getString(2));
     dtDetail.setPRODUCT_NAME(rs.getString(3));
     dtDetail.setWELCOME_MESSAGE(rs.getString(4));
     dtDetail.setFAREWELL_MESSAGE(rs.getString(5));
     dtDetail.setSERVICE_NUMBER(rs.getString(6));
     dtDetail.setREGISTER_KEYWORD(rs.getString(7));
     dtDetail.setCANCEL_KEYWORD(rs.getString(8));
     dtDetail.setCREATED_TIME(DateUtil.toDate(rs.getString(9), "dd/MM/yyyy HH:mm:ss"));
     dtDetail.setEFFECTIVE_TIME(DateUtil.toDate(rs.getString(10), "dd/MM/yyyy HH:mm:ss"));
     dtDetail.setEXPIRY_TIME(DateUtil.toDate(rs.getString(11), "dd/MM/yyyy HH:mm:ss"));
     dtDetail.setStrCREATED_TIME(rs.getString(9));
     dtDetail.setStrEFFECTIVE_TIME(rs.getString(10));
     dtDetail.setStrEXPIRY_TIME(rs.getString(11));
     dtDetail.setCONTENT_BUILDER_CLASS_ID(rs.getString(12));
     dtDetail.setCONTENT_BUILDER_TYPE(rs.getString(13));
     dtDetail.setCONTENT_MODE(rs.getString(14));
     dtDetail.setCHARGING_MODE(rs.getString(15));
     dtDetail.setCHARGING_CLASS_ID(rs.getString(16));
     dtDetail.setSTATUS(rs.getString(17));
     dtDetail.setSERVICE_ID(rs.getString(18));
     returnData.add(dtDetail);
     }
     } catch (Exception ex) {
     ex.printStackTrace();
     } finally {
     rs.close();
     stmt.close();
     //            close();
     }
     return returnData;
     }*/
    public ETT_Product insert(ETT_Product ETT_Product) throws Exception {
        Connection cn = null;
        PreparedStatement stmt = null;
//        ResultSet rs = null;
        String strSQL = " insert into sdp_products a(a.product_id, a.sdp_product_code, a.product_name, a.service_id, "
                + " a.welcome_message, a.farewell_message, a.service_number, "
                + " a.register_keyword, a.cancel_keyword, a.created_time, "
                + " a.effective_time, a.expiry_time, a.content_builder_class_id, "
                + " a.content_builder_type, a.content_mode, "
                + " a.charging_mode, a.charging_class_id, a.status, a.content_type, a.effective_duration, a.build_procedure,"
                + " a.build_content, a.build_user_content, a.content_concatenation) "
                + " values (?,?,?,?,?,?,?,?,?,to_date(?,'dd/mm/yyyy hh24:mi:ss'),"
                + "to_date(?,'dd/mm/yyyy hh24:mi:ss'),to_date(?,'dd/mm/yyyy hh24:mi:ss'),?,?,?,?,?,?,?,?,?,?,?,?) ";
        try {
//            open();
            cn = AppServer.getConnection();
            SysId = Database.getSequenceValue(cn, "sdp_product_seq");
            stmt = cn.prepareStatement(strSQL);
            stmt.setString(1, SysId);
            stmt.setString(2, ETT_Product.getSDP_PRODUCT_CODE());
            stmt.setString(3, ETT_Product.getPRODUCT_NAME());
            stmt.setString(4, ETT_Product.getSERVICE_ID());
            stmt.setString(5, ETT_Product.getWELCOME_MESSAGE());
            stmt.setString(6, ETT_Product.getFAREWELL_MESSAGE());
            stmt.setString(7, ETT_Product.getSERVICE_NUMBER());
            stmt.setString(8, ETT_Product.getREGISTER_KEYWORD());
            stmt.setString(9, ETT_Product.getCANCEL_KEYWORD());
            stmt.setString(10, df.format(ETT_Product.getCREATED_TIME()));
            stmt.setString(11, df.format(ETT_Product.getEFFECTIVE_TIME()));
            if(ETT_Product.getEXPIRY_TIME() == null || ETT_Product.getEXPIRY_TIME().toString().equalsIgnoreCase("")){
                stmt.setString(12, "");
            }
            else stmt.setString(12, df.format(ETT_Product.getEXPIRY_TIME()));
            stmt.setString(13, ETT_Product.getCONTENT_BUILDER_CLASS_ID());
            stmt.setString(14, ETT_Product.getCONTENT_BUILDER_TYPE());
            stmt.setString(15, ETT_Product.getCONTENT_MODE());
            stmt.setString(16, ETT_Product.getCHARGING_MODE());
            stmt.setString(17, ETT_Product.getCHARGING_CLASS_ID());
            stmt.setString(18, ETT_Product.getSTATUS());
            stmt.setString(19, ETT_Product.getContentType());
//            stmt.setString(20, ETT_Product.getEffective_duration());
            stmt.setInt(20, ETT_Product.getEffective_duration());
            if(ETT_Product.getBuild_procedure() == null || ETT_Product.getBuild_procedure().equalsIgnoreCase("")){
                stmt.setString(21, "");
            }
            else stmt.setString(21, ETT_Product.getBuild_procedure());
            stmt.setInt(22, ETT_Product.getBuild_content());
            stmt.setInt(23, ETT_Product.getBuild_user_content());
            stmt.setInt(24, ETT_Product.getContent_concatenation());
            stmt.execute();
            stmt.close();
            ETT_Product.setPRODUCT_ID(SysId);
            ETT_Product.setStrCREATED_TIME(df.format(ETT_Product.getCREATED_TIME()));
            ETT_Product.setStrEFFECTIVE_TIME(df.format(ETT_Product.getEFFECTIVE_TIME()));
            ETT_Product.setStrEXPIRY_TIME(df.format(ETT_Product.getEXPIRY_TIME()));
            /////////INSERT CONTENT MODE
//            strSQL = " insert into sdp_system_content_mode (system_id, content_mode) values(?,?) ";
//            cn.setAutoCommit(false);
//            stmt = cn.prepareStatement(strSQL);
//            for (int i = 0; i < ContentMode.size(); i++) {
//                stmt.setString(1, SysId);
//                stmt.setString(2, ContentMode.get(i));
//                stmt.addBatch();
//                stmt.executeBatch();
//                cn.commit();
//            }
//            cn.setAutoCommit(true);
//            close(stmt);
            /////////INSERT CHARGING MODE
//            strSQL = " insert into sdp_system_charging_mode (system_id, charging_mode) values(?,?) ";
//            cn.setAutoCommit(false);
//            stmt = cn.prepareStatement(strSQL);
//            for (int i = 0; i < CharMode.size(); i++) {
//                stmt.setString(1, SysId);
//                stmt.setString(2, CharMode.get(i));
//                stmt.addBatch();
//                stmt.executeBatch();
//                cn.commit();
//            }
//            cn.setAutoCommit(true);
//            close(stmt);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
//            close(rs);
//            close(stmt);
//            close();
            Database.closeObject(cn);
        }
        return ETT_Product;
    }

    public void update(ETT_Product ETT_Product) throws Exception {
        Connection cn = null;
        PreparedStatement stmt = null;
//        ResultSet rs = null;
        String strSQL =
                " update sdp_products a set a.sdp_product_code =?, a.product_name=?, "
                + " a.welcome_message=?, a.farewell_message=?, a.service_number=?, "
                + " a.register_keyword=?, a.cancel_keyword=?, a.created_time=to_date(?,'dd/mm/yyyy hh24:mi:ss'), "
                + " a.effective_time=to_date(?,'dd/mm/yyyy hh24:mi:ss'), a.expiry_time=to_date(?,'dd/mm/yyyy hh24:mi:ss'), a.content_builder_class_id=?, "
                + " a.content_builder_type=?, a.content_mode=?, "
                + " a.charging_mode=?, a.charging_class_id=?, a.status = ?, a.content_type = ?, a.effective_duration = ?,"
                + " a.build_procedure = ?, a.build_content = ?, a.build_user_content = ?, a.content_concatenation = ? "
                + " where a.service_id = ? and a.product_id = ? ";
        try {
//            open();
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            stmt.setString(1, ETT_Product.getSDP_PRODUCT_CODE());
            stmt.setString(2, ETT_Product.getPRODUCT_NAME());
            stmt.setString(3, ETT_Product.getWELCOME_MESSAGE());
            stmt.setString(4, ETT_Product.getFAREWELL_MESSAGE());
            stmt.setString(5, ETT_Product.getSERVICE_NUMBER());
            stmt.setString(6, ETT_Product.getREGISTER_KEYWORD());
            stmt.setString(7, ETT_Product.getCANCEL_KEYWORD());
            stmt.setString(8, df.format(ETT_Product.getCREATED_TIME()));
            stmt.setString(9, df.format(ETT_Product.getEFFECTIVE_TIME()));
            if(ETT_Product.getEXPIRY_TIME() == null || ETT_Product.getEXPIRY_TIME().toString().equalsIgnoreCase("")){
                stmt.setString(10, "");
            }
            else stmt.setString(10, df.format(ETT_Product.getEXPIRY_TIME()));
            stmt.setString(11, ETT_Product.getCONTENT_BUILDER_CLASS_ID());
            stmt.setString(12, ETT_Product.getCONTENT_BUILDER_TYPE());
            stmt.setString(13, ETT_Product.getCONTENT_MODE());
            stmt.setString(14, ETT_Product.getCHARGING_MODE());
            stmt.setString(15, ETT_Product.getCHARGING_CLASS_ID());
            stmt.setString(16, ETT_Product.getSTATUS());
            stmt.setString(17, ETT_Product.getContentType());
//            stmt.setString(18, ETT_Product.getEffective_duration());            
            stmt.setInt(18, ETT_Product.getEffective_duration());
            //them 4 cot
            if(ETT_Product.getBuild_procedure() == null || ETT_Product.getBuild_procedure().equalsIgnoreCase("")){
                stmt.setString(19, "");
            }
            else stmt.setString(19, ETT_Product.getBuild_procedure());
            stmt.setInt(20, ETT_Product.getBuild_content());
            stmt.setInt(21, ETT_Product.getBuild_user_content());
            stmt.setInt(22, ETT_Product.getContent_concatenation());
            
            stmt.setString(23, ETT_Product.getSERVICE_ID());
            stmt.setString(24, ETT_Product.getPRODUCT_ID());            
            stmt.execute();
            stmt.close();
            //
//            strSQL = " insert into sdp_system_content_mode (system_id, content_mode) values(?,?) ";
//            cn.setAutoCommit(false);
//            stmt = cn.prepareStatement(strSQL);
//            for (int i = 0; i < ContentMode.size(); i++) {
//                stmt.setString(1, SysId);
//                stmt.setString(2, ContentMode.get(i));
//                stmt.addBatch();
//                stmt.executeBatch();
//                cn.commit();
//            }
////            cn.setAutoCommit(true);
//            close(stmt);
//            /////////INSERT CHARGING MODE
//            strSQL = " insert into sdp_system_charging_mode (system_id, charging_mode) values(?,?) ";
//            cn.setAutoCommit(false);
//            stmt = cn.prepareStatement(strSQL);
//            for (int i = 0; i < CharMode.size(); i++) {
//                stmt.setString(1, SysId);
//                stmt.setString(2, CharMode.get(i));
//                stmt.addBatch();
//                stmt.executeBatch();
//                cn.commit();
//            }
//            cn.setAutoCommit(true);
//            close(stmt);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
//            close(rs);
//            close(stmt);
//            close();
            Database.closeObject(cn);
        }
    }

    public void delete(ETT_Product ETT_Product) throws Exception {
        Connection cn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String strSQL =
                " delete from sdp_products where service_id = ? and product_id = ? ";
        try {
//            open();
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            stmt.setString(1, ETT_Product.getSERVICE_ID());
            stmt.setString(2, ETT_Product.getPRODUCT_ID());
            stmt.executeUpdate();
        } finally {
//            close(rs);
            stmt.close();
//            close();
            Database.closeObject(cn);
        }
    }

    /*    public void remove() throws Exception {
     Connection cn = null;
     PreparedStatement stmt = null;
     ResultSet rs = null;
     String strSQL = " delete from sdp_system_charging_mode where charging_mode = ? and system_id = ? ";
     try {
     //            open();
     cn = AppServer.getConnection();
     stmt = cn.prepareStatement(strSQL);
     for (int i = 0; i < CharMode.size(); i++) {
     stmt.setString(1, CharMode.get(i));
     stmt.setString(2, SysId);
     stmt.execute();
     }
     stmt.close();
     ///
     strSQL = " delete from sdp_system_content_mode where content_mode = ? and system_id = ? ";
     cn.setAutoCommit(false);
     stmt = cn.prepareStatement(strSQL);
     for (int i = 0; i < ContentMode.size(); i++) {
     stmt.setString(1, ContentMode.get(i));
     stmt.setString(2, SysId);
     stmt.addBatch();
     stmt.executeBatch();
     cn.commit();
     }
     cn.setAutoCommit(true);
     stmt.close();
     } finally {
     //            close(rs);
     //            close();
     }
     } */
    public List getlsContentBuilderClass() throws Exception {
        Connection cn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List returnData = new ArrayList<ETT_TreeProduct>();
        String strSQL = " select a.content_builder_class_id,a.name from sdp_content_builder_classes a order by a.content_builder_class_id,a.name ";
        try {
//            open();
            // Get User list
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            rs = stmt.executeQuery();
            while (rs.next()) {
                ETT_TreeProduct tree = new ETT_TreeProduct();
                tree.setID(rs.getString(1));
                tree.setNAME(rs.getString(2));
                returnData.add(tree);
            }
            rs.close();
            stmt.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
//            close();
            Database.closeObject(cn);
        }
        return returnData;
    }

    public List getlsChargingClass() throws Exception {
        Connection cn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List returnData = new ArrayList<ETT_TreeProduct>();
        String strSQL = " select a.charging_class_id,a.name from sdp_charging_classes a order by a.charging_class_id,a.name ";
        try {
//            open();
            // Get User list
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            rs = stmt.executeQuery();
            while (rs.next()) {
                ETT_TreeProduct tree = new ETT_TreeProduct();
                tree.setID(rs.getString(1));
                tree.setNAME(rs.getString(2));
                returnData.add(tree);
            }
            rs.close();
            stmt.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
//            close();
            Database.closeObject(cn);
        }
        return returnData;
    }

    public List getListChargingMode(ETT_TreeProduct ETT_TreeProduct) throws Exception {
        Connection cn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List returnData = new ArrayList<ETT_Product>();
        String strSQL = "";
        try {
//            open();
            strSQL = " select a.charging_mode from sdp_system_charging_mode a where a.system_id = ? ";
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            ///////////
            stmt.setString(1, ETT_TreeProduct.getPARENT_ID());

            rs = stmt.executeQuery();
            while (rs.next()) {
                ETT_TreeProduct dtDetail = new ETT_TreeProduct();
                dtDetail.setNAME(rs.getString(1));
                returnData.add(dtDetail);
            }
        } catch (Exception ex) {
            ex.fillInStackTrace();
        } finally {
//            close();
            rs.close();
            stmt.close();
            Database.closeObject(cn);
        }
        return returnData;
    }

    public List getListContentMode(ETT_TreeProduct ETT_TreeProduct) throws Exception {
        Connection cn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List returnData = new ArrayList<ETT_Product>();
        String strSQL = "";
        try {
//            open();
            strSQL = " select distinct a.content_mode,DECODE (a.content_mode,1,'GET',2,'PUSH','SMS') from sdp_system_content_mode a where a.system_id = ? ";
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            ///////////
            stmt.setString(1, ETT_TreeProduct.getPARENT_ID());

            rs = stmt.executeQuery();
            while (rs.next()) {
                ETT_TreeProduct dtDetail = new ETT_TreeProduct();
                dtDetail.setID(rs.getString(1));
                dtDetail.setNAME(rs.getString(2));
                returnData.add(dtDetail);
            }
        } catch (Exception ex) {
            ex.fillInStackTrace();
        } finally {
//            close();
            rs.close();
            stmt.close();
            Database.closeObject(cn);
        }
        return returnData;
    }
        
    
    //code by thangder    
    
    public static String getProductIdByCode(String code){
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String strSQL = "";
        try {
            strSQL = "select * from sdp_products where sdp_product_code = '"+code+"'";
            cn = AppServer.getConnection();
            st = cn.createStatement();
            rs = st.executeQuery(strSQL);
            if(rs.next()){
                return rs.getString(1);
            }
            return "";
        } catch (Exception ex) {
            ex.fillInStackTrace();
        } finally {
            Database.closeObject(rs);
            Database.closeObject(st);
            Database.closeObject(cn);
        }
        return "";
    }
    
    public static ArrayList<Product_Content> listProductContent(String pCode){
        ArrayList<Product_Content> list = new ArrayList<Product_Content>();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String strSQL = "";
        try {
            strSQL = "select b.product_name,c.content_template_name,to_char(a.from_date,'dd-mm-yyyy'),to_char(a.to_date,'dd-mm-yyyy'),a.id"
                    + " from sdp_product_contents a,sdp_products b,md_content_templates c"
                    + " where (a.product_id = b.product_id(+)) and (a.content_template_id = c.content_template_id(+))"
                    + " and b.sdp_product_code = '"+pCode+"'";
            cn = AppServer.getConnection();
            st = cn.createStatement();
            rs = st.executeQuery(strSQL);
            while(rs.next()){
                Product_Content p = new Product_Content();
                p.setProduct_name(rs.getString(1));
                p.setContent_template_name(rs.getString(2));
                p.setFrom_date(rs.getString(3));                
                if(rs.getString(4) == null){
                    p.setTo_date("");
                }
                else p.setTo_date(rs.getString(4));
                p.setId(rs.getString(5));
                list.add(p);
            }
            return list;
        } catch (Exception ex) {
            ex.fillInStackTrace();
        } finally {
            Database.closeObject(rs);
            Database.closeObject(st);
            Database.closeObject(cn);
        }
        return null;
    }
    
    public static boolean remove(String id){
        Connection cn = null;
        Statement st = null;
        String strSQL = "";
        try {
            strSQL = "delete from sdp_product_contents where id = "+id+"";
            cn = AppServer.getConnection();
            st = cn.createStatement();
            int i = st.executeUpdate(strSQL);
            if(i > 0){
                return true;
            }
            return false;
        } catch (Exception ex) {
            ex.fillInStackTrace();
        } finally {
            Database.closeObject(st);
            Database.closeObject(cn);
        }
        return false;
    }
    
    public static boolean update(Product_Content p){
        Connection cn = null;
        Statement st = null;
        String strSQL = "";
        try {
            strSQL = "update sdp_product_contents"
                    + " set content_template_id = "+ContentTemplate.getIdByName(p.getContent_template_name())+","
                    + " from_date = to_date('"+p.getFrom_date()+"','dd-mm-yyyy'),"
                    + " to_date = to_date('"+p.getTo_date()+"','dd-mm-yyyy')"
                    + " where id = "+p.getId()+"";
            cn = AppServer.getConnection();
            st = cn.createStatement();
            int i = st.executeUpdate(strSQL);
            if(i > 0){
                return true;
            }
            return false;
        } catch (Exception ex) {
            ex.fillInStackTrace();
            ex.printStackTrace();
        } finally {
            Database.closeObject(st);
            Database.closeObject(cn);
        }
        return false;
    }
    
    public static boolean insertPContent(Product_Content p,String pId,String ctId){
        Connection cn = null;
        Statement st = null;
        String strSQL = "";
        try {
            strSQL = "insert into sdp_product_contents "
                    + " values(seq_common.nextval,"+pId +","+ctId+",TO_DATE('"+p.getFrom_date()+"','dd-mm-yyyy'),TO_DATE('"+p.getTo_date()+"','dd-mm-yyyy'))";
            cn = AppServer.getConnection();
            st = cn.createStatement();
            int i = st.executeUpdate(strSQL);
            if(i > 0){
                return true;
            }
            return false;
        } catch (Exception ex) {
            ex.fillInStackTrace();
            ex.printStackTrace();
        } finally {
            Database.closeObject(st);
            Database.closeObject(cn);
        }
        return false;
    }
}
